﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace CNotebook
{
    public class WarehouseManager:ConnectionManager
    {

        public bool IsWarehouseCodeExists(string sCode)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM WAREHOUSE WHERE Code=@WarehouseCode";

                oCommand.Parameters.Add(new SqlParameter("@WarehouseCode", sCode));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                int iCount = ds.Tables[0].Rows.Count;


                if (iCount > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }
            catch
            {
                return false;
            }
        }

        public bool IsWarehouseNameExists(string sName)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM WAREHOUSE WHERE Description=@WarehouseName";


                oCommand.Parameters.Add(new SqlParameter("@WarehouseName", sName));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                int iCount = ds.Tables[0].Rows.Count;


                if (iCount > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }
            catch
            {
                return false;
            }
        }

        public void AddWarehouse(WarehouseUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO WAREHOUSE (Code, Dscription, AcctCode, AcctName, DocStatus) VALUES (@Code,  @Description, @AcctCode, @AcctName, @DocStatus)";
            oCommand.Parameters.Add(new SqlParameter("@Code", oUnit.Code));
            oCommand.Parameters.Add(new SqlParameter("@Description", oUnit.Description));
            oCommand.Parameters.Add(new SqlParameter("@AcctCode", oUnit.AcctCode));
            oCommand.Parameters.Add(new SqlParameter("@AcctName", oUnit.AcctName));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.Status));

            oCommand.ExecuteNonQuery();
        }

        public void UpdateWarehouse(WarehouseUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE WAREHOUSE SET Dscription=@Dscription, AcctCode=@AcctCode, AcctName=@AcctName, DocStatus=@DocStatus WHERE Code=@Code";
            oCommand.Parameters.Add(new SqlParameter("@Code", oUnit.Code));
            oCommand.Parameters.Add(new SqlParameter("@Dscription", oUnit.Description));
            oCommand.Parameters.Add(new SqlParameter("@AcctCode", oUnit.AcctCode));
            oCommand.Parameters.Add(new SqlParameter("@AcctName", oUnit.AcctName));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.Status));

            oCommand.ExecuteNonQuery();
        }

        public DataTable GetWarehouse(string WhseCode)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT * FROM WAREHOUSE WHERE Code=@Code";
                _COMMAND.Parameters.Add(new SqlParameter("@Code", WhseCode));
                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetWarehouse()
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT * FROM WAREHOUSE";

                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetWarehouseByCode(string sCode)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                sCode = '%' + sCode.Trim() + '%';

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT * FROM WAREHOUSE WHERE Code LIKE @WhsCode";
                _COMMAND.Parameters.Add(new SqlParameter("@WhsCode", sCode));

                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetWarehouseByDescription(string sDescription)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                sDescription = '%' + sDescription.Trim() + '%';

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT * FROM WAREHOUSE WHERE Dscription LIKE @Dscription";
                _COMMAND.Parameters.Add(new SqlParameter("@Dscription", sDescription));


                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetWarehouseAndExclude(string sExcludeWarehouse)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT Code, Dscription FROM WAREHOUSE WHERE Code NOT IN (@WHCode)";
                _COMMAND.Parameters.Add(new SqlParameter("@WHCode", sExcludeWarehouse));

                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }
}
